package com.jdbc.www;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DVD_manager {
	//新增开始
	public void add(){
		jdbc.jdbcs();
		System.out.println("请输入新增的设备名：");
		String name1 = ScannerStr.scString();
		String sql = "select name from dvd";
		try {
			//先把表中存在的设备名都查询出来，以便后边的查重
			PreparedStatement pst = jdbc.conn.prepareStatement(sql);
			ResultSet rs =pst.executeQuery();
			
			//System.out.println(rs.getFetchSize());
			if(rs.next()){//如果结果集不为空就执行 查重
				//关系到集合
				int j=0;
				for(;j<rs.getFetchSize();j++){
					if(rs.getString("name").equals(name1)){
						System.out.println("该设备已存在请勿重复添加！！！");
						break;
					}
				}
				if(j == rs.getFetchSize()){
					String sql1="insert into dvd (id,name,lend_times,action)values("
							+ "dvd_auto_increment.nextval,'"+name1+"',0,'未借出')";
					PreparedStatement pst1 = jdbc.conn.prepareStatement(sql1);
					int i=pst1.executeUpdate();
					if(i == 1){
						System.out.println("新增成功！！！");
						//b=false;
					}
				}
			}else{//结果集为空直接插入
				String sql1="insert into dvd (id,name,lend_times,action)values("
						+ "dvd_auto_increment.nextval,'"+name1+"',0,'未借出')";
				PreparedStatement pst1 = jdbc.conn.prepareStatement(sql1);
				int i=pst1.executeUpdate();
				if(i == 1){
					System.out.println("新增成功！！！");
					//b=false;
				}
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//新增结束
	//查看开始
	public void show(){
		jdbc.jdbcs();
		String sql2="select id,name,lend_times,to_char(last_lend_time,'yyyy-MM-dd HH:mi:ss')last_lend_times,action  from dvd";
		try {
			PreparedStatement pst = jdbc.conn.prepareStatement(sql2);
			ResultSet rs = pst.executeQuery();
			System.out.println("编号\t设备名\t借出次数\t最后一次借出时间\t借出状态");
			while(rs.next()){
				System.out.println(rs.getInt("id")+"\t"+rs.getString("name")+"\t"+rs.getInt("lend_times")+"\t"+rs.getString("last_lend_times")+"\t"+rs.getString("action"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//查看结束
	//删除dvd开始
	public void deletes(){
		jdbc.jdbcs();
		System.out.println("请输入要删除的设备名：");
		String name = ScannerStr.scString();
		String sql_l = "select * from dvd where name=?";
		try {
			PreparedStatement pst = jdbc.conn.prepareStatement(sql_l);
			pst.setString(1, name);
			ResultSet rs = pst.executeQuery();
			//根据名字先查询出该数据，然后对该数据进行判断   如果不为空取出id 用于后边的删除条件
			int ids = 0;
			while(rs.next()){
				if(rs.getString("name").equals(name) && rs.getString("action").equals("未借出")){
					ids = rs.getInt("id");
				}
			}
			if(ids > 0){//如果进行到这一步，就说明存在该数据，进行删除工作
				String sql_de = "delete from dvd where id=?";
				PreparedStatement pst1 = jdbc.conn.prepareStatement(sql_de);
				pst1.setInt(1, ids);
				int k = pst1.executeUpdate();//
				if(k > 0){
					System.out.println("删除设备成功！！！");
				}else{
					System.out.println("删除设备失败！！！");
				}
			}else{
				System.out.println("删除设备失败！！！");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//删除did结束
	//借出dvd开始
	public void lend(){
		jdbc.jdbcs();
		System.out.println("请输入借出设备的名称");
		String name = ScannerStr.scString();
		String sql3 = "select * from dvd";
		try {
			PreparedStatement pst= jdbc.conn.prepareStatement(sql3);
			ResultSet rs = pst.executeQuery();
			//在借出前先对设备进行检查  包括是否存在、是否已借出，如果借出的话是不能进行借出操作的
			boolean bool = false;
			//System.out.println(bool);
			//System.out.println(rs.getFetchSize());
			int dvd_id=0;
			while(rs.next()){
				if(rs.getString("name").equals(name) && rs.getString("action").equals("未借出")){
					bool=true;
					//System.out.println(bool);
					dvd_id = rs.getInt("id");
					break;
				}
			}
			/*for(int i=0;i<rs.getFetchSize();i++){
				System.out.println(i);
				if(rs.getString("name").equals(name) && rs.getString("action").equals("未借出")){
					bool=true;
					System.out.println(bool);
					dvd_id = rs.getInt("id");
					break;
				}
			}*/
			if(bool){//进行到这一步说明已经可以进行借出操作  即修改设备的信息
				String sql4 = "select * from dvd where id = "+dvd_id;
				//System.out.println(sql4);
				PreparedStatement pst1 = jdbc.conn.prepareStatement(sql4);
				ResultSet rs1 = pst1.executeQuery();//查询出要借出设备的信息
				int ldt = 0;//用来存储借出次数
				while(rs1.next()){
					ldt = rs1.getInt("lend_times");
				}
				//开始执行插入语句
				String sql5 = "update dvd set lend_times=?,last_lend_time=sysdate,action=? where id = ?";
				PreparedStatement pst2 = jdbc.conn.prepareStatement(sql5);
				pst2.setInt(1, ++ldt);
				pst2.setString(2, "借出");
				pst2.setInt(3, dvd_id);
				int k = pst2.executeUpdate();
				if(k>0){
					System.out.println("借出成功！！！");
				}else{
					System.out.println("借出失败！！！");
				}
			}else{
				System.out.println("设备名或状态有误，请重新输入！！！");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//借出did结束
	//归还dvd开始
	public void lend_back(){
		jdbc.jdbcs();
		System.out.println("请输入要归还的设备名：");
		String name = ScannerStr.scString();
		String sql6 = "select * from dvd where name=?";
		//System.out.println(sql6);
		try {
			PreparedStatement pst = jdbc.conn.prepareStatement(sql6);
			pst.setString(1, name);
			ResultSet rst = pst.executeQuery();
			//System.out.println("1集合："+rst.next());
			/*if(rst.getFetchSize()>0){
				System.out.println(1234);
			}*/
			//System.out.println(rst.next());
			
			if(rst.next()){
				//System.out.println(123);
				boolean act = false;
				int id=0;
				while(!act){
					if(rst.getString("name").equals(name) && rst.getString("action").equals("借出")){
						act = true;
						id=rst.getInt("id");
					}else{
						System.out.println("库中没有该设备,或者该设备已借出！！！");
						break;
					}
				}
				//System.out.println("2判断："+act);
				if(act){
					String sql7 ="update dvd set action=? where id=?";
					PreparedStatement pst1 = jdbc.conn.prepareStatement(sql7);
					pst1.setString(1, "未借出");
					pst1.setInt(2, id);
					int k = pst1.executeUpdate();
					if(k > 0){
						System.out.println("归还成功！！！");
					}else{
						System.out.println("归还失败！！！");
					}
				}/*else{
					System.out.println("归还设备名不正确或者该设备未借出，请重新输入！！！");
				}*/
				
			}else{
				System.out.println("归还设备名不正确，请重新输入！！！");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//归还did结束
	//借出列表dvd开始
	public void lend_list(){
		jdbc.jdbcs();
		String sql8 = "select * from dvd where action='借出' order by lend_times desc";
		try {
			PreparedStatement pst = jdbc.conn.prepareStatement(sql8);
			ResultSet rs = pst.executeQuery();
			System.out.println("编号\t设备名\t借出次数\t最后一次借出时间\t借出状态");
			while(rs.next()){
				System.out.println(rs.getInt("id")+"\t"+rs.getString("name")+"\t"+rs.getInt("lend_times")+"\t"+rs.getString("last_lend_time")+"\t"+rs.getString("action"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//借出列表did结束
}
